/*
 Navicat Premium Dump SQL

 Source Server         : local-mysql-docker
 Source Server Type    : MySQL
 Source Server Version : 80200 (8.2.0)
 Source Host           : localhost:3306
 Source Schema         : student_grade_manage

 Target Server Type    : MySQL
 Target Server Version : 80200 (8.2.0)
 File Encoding         : 65001

 Date: 17/06/2025 10:57:17
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `student_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `chinese_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `english_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tel` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stu_class` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `semester` int NOT NULL,
  `mid_marks` decimal(5,2) DEFAULT NULL,
  `end_marks` decimal(5,2) DEFAULT NULL,
  `stu_status` char(1) COLLATE utf8mb4_unicode_ci DEFAULT 'A',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`),
  CONSTRAINT `students_chk_1` CHECK ((`semester` between 1 and 8)),
  CONSTRAINT `students_chk_2` CHECK ((`stu_status` in (_utf8mb4'A',_utf8mb4'L')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of students
-- ----------------------------
BEGIN;
INSERT INTO `students` (`student_id`, `chinese_name`, `english_name`, `tel`, `stu_class`, `semester`, `mid_marks`, `end_marks`, `stu_status`, `created_at`, `updated_at`) VALUES ('202501001', '雷雷', 'leilei', '15878789898', '计算机科学与技术1班', 1, 80.00, 95.00, 'A', '2025-06-17 10:45:23', '2025-06-17 10:54:19');
INSERT INTO `students` (`student_id`, `chinese_name`, `english_name`, `tel`, `stu_class`, `semester`, `mid_marks`, `end_marks`, `stu_status`, `created_at`, `updated_at`) VALUES ('202501002', '黄瑶', 'huangyao', '15878789891', '计算机科学与技术1班', 1, NULL, NULL, 'A', '2025-06-17 10:47:31', '2025-06-17 10:48:02');
INSERT INTO `students` (`student_id`, `chinese_name`, `english_name`, `tel`, `stu_class`, `semester`, `mid_marks`, `end_marks`, `stu_status`, `created_at`, `updated_at`) VALUES ('202502001', '李斯', 'lisi', '15878789893', '计算机科学与技术2班', 1, NULL, NULL, 'A', '2025-06-17 10:48:22', '2025-06-17 10:50:24');
INSERT INTO `students` (`student_id`, `chinese_name`, `english_name`, `tel`, `stu_class`, `semester`, `mid_marks`, `end_marks`, `stu_status`, `created_at`, `updated_at`) VALUES ('202502002', '刘昊然', 'Liu', '15878789896', '计算机科学与技术2班', 1, NULL, NULL, 'A', '2025-06-17 10:56:00', '2025-06-17 10:56:41');
COMMIT;

-- ----------------------------
-- Table structure for teachers
-- ----------------------------
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
  `teacher_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `chinese_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `english_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tel` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `class_managed` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of teachers
-- ----------------------------
BEGIN;
INSERT INTO `teachers` (`teacher_id`, `chinese_name`, `english_name`, `tel`, `class_managed`, `created_at`, `updated_at`) VALUES ('202501009', '1班教师', 'lily', '15878789890', '计算机科学与技术1班', '2025-06-17 10:46:10', '2025-06-17 10:46:41');
INSERT INTO `teachers` (`teacher_id`, `chinese_name`, `english_name`, `tel`, `class_managed`, `created_at`, `updated_at`) VALUES ('202502009', '章老师', 'zhang', '15878789895', '计算机科学与技术2班', '2025-06-17 10:49:14', '2025-06-17 10:49:31');
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` enum('ADMIN','TEACHER','STUDENT') COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (1, 'admin', '123456', 'ADMIN', '2025-06-15 17:08:01', '2025-06-15 17:08:01');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (11, '202501001', '123456', 'STUDENT', '2025-06-17 10:45:23', '2025-06-17 10:45:23');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (12, '202501009', '123456', 'TEACHER', '2025-06-17 10:46:10', '2025-06-17 10:46:10');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (13, '202501002', '123456', 'STUDENT', '2025-06-17 10:47:31', '2025-06-17 10:47:31');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (14, '202502001', '123456', 'STUDENT', '2025-06-17 10:48:22', '2025-06-17 10:48:22');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (15, '202502009', '123456', 'TEACHER', '2025-06-17 10:49:14', '2025-06-17 10:49:14');
INSERT INTO `users` (`id`, `username`, `password`, `role`, `created_at`, `updated_at`) VALUES (16, '202502002', '123456', 'STUDENT', '2025-06-17 10:56:00', '2025-06-17 10:56:00');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
